﻿using Model;
using System;
using System.Data;
using System.Text;

namespace BLL
{
    public class Qs_EntrustBLL : EntityBLL<Qs_Entrust>
    {
        public int InsertModel(Qs_Entrust model, string operName)
        {
            model.N_Entrust_Id = GetSequence("N_Entrust_ID");
            var sql = string.Format(@"INSERT INTO QS_ENTRUST(
	N_ENTRUST_ID,
	N_PRINCIPAL,
	N_USER_ID,
	D_NTRUST_START_DATE,
	C_ENTRUST_STATE,
	N_ENTRUST_REMARKS,
	D_NTRUST_OPERATING_TIME,
	COMPANY_ID,
	D_NTRUST_ENDDATA
)
VALUES(
	{0},
	{1},
	{2},
	TO_DATE('{3}','YYYY-MM-DD HH24:MI:SS'),
	'{4}',
	'{5}',
	TO_DATE('{6}','YYYY-MM-DD HH24:MI:SS'),
	{7},
	TO_DATE('{8}','YYYY-MM-DD HH24:MI:SS')
)",
  model.N_Entrust_Id,
  model.N_Principal,
  model.N_User_Id,
  model.D_Ntrust_start_date.ToString("yyyy-MM-dd HH:mm:ss"),
  model.C_Entrust_state,
  model.N_Entrust_remarks,
  model.D_ntrust_operating_time.ToString("yyyy-MM-dd HH:mm:ss"),
  model.Company_Id,
  model.D_ntrust_enddata.ToString("yyyy-MM-dd HH:mm:ss")
  );
            return base.NoQuery(sql, operName);
        }

        public int UpdateModel(Qs_Entrust model, string operName)
        {
            string sql = string.Format(@"UPDATE THKQS.Qs_Entrust SET C_Entrust_state =" + model.C_Entrust_state + ", N_Entrust_remarks='" + model.N_Entrust_remarks + @"' WHERE N_Entrust_Id =" + model.N_Entrust_Id);
            return base.NoQuery(sql, operName);
        }

        public int Update(string id, string EndDate)
        {
            string sql = "Update qs_entrust e set e.c_entrust_state=1,e.d_ntrust_enddata=to_date('" + EndDate + "','yyyy-MM-dd') where e.n_entrust_id=" + id;
            return base.NoQuery(sql, "");
        }

        public Qs_Entrust SelectModelById(string id)
        {
            string sql = string.Format(@"SELECT
    e.N_Entrust_Id,
    e.N_Principal,
    e.N_User_Id,
    e.D_Ntrust_start_date,
    e.C_Entrust_state,
    e.N_Entrust_remarks,
    e.D_ntrust_operating_time
FROM
    THKQS.Qs_Entrust e
WHERE
    e.N_Entrust_Id=" + id);

            DataTable dt = base.Query(sql, "", "", "", "");// DbHelperSQL.GetDataTable(sql);
            Qs_Entrust model = null;

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    model = new Qs_Entrust();
                    model.N_Entrust_Id = Convert.ToDecimal(item[0]);
                    model.N_Principal = Convert.ToDecimal(item[1]);
                    model.N_User_Id = Convert.ToDecimal(item[2]);

                    model.D_Ntrust_start_date = Convert.ToDateTime(item[3]);
                    model.C_Entrust_state = item[4].ToString();

                    model.N_Entrust_remarks = item[5].ToString();
                    model.D_ntrust_operating_time = Convert.ToDateTime(item[6]);
                }
            }
            return model;
        }

        public DataTable SelectModel(decimal userid)
        {
            //SQL 语句
            string sql = string.Format(@"select e.n_entrust_id,
			 us.user_name,
			 u.user_name user_names,
			 e.d_ntrust_start_date,
			 decode(e.c_entrust_state, 0, '是', 1, '否') c_entrust_state,
			 e.n_entrust_remarks,
			 e.d_ntrust_operating_time,
             e.d_ntrust_enddata
	from qs_entrust e, sys_user u, sys_user us
 where us.user_id = e.n_principal
	 and u.user_id = e.n_user_id
	 and e.n_principal=" + userid);

            sql += " order by e.n_entrust_id desc";
            //执行sql并接收返回结果
            return base.Query(sql, "", "", "", "");
        }

        public DataTable SelectModelLike(decimal companyid, string name, string state)
        {
            //SQL 语句
            string sql = string.Format(@" select n_entrust_id,
			 user_name,
			 user_names,
			 d_ntrust_start_date,
			 c_entrust_state,
			 n_entrust_remarks,
			 d_ntrust_operating_time,d_ntrust_enddata from ( select e.n_entrust_id,
			 us.user_name,
			 u.user_name user_names,
			 e.d_ntrust_start_date,
			 decode(e.c_entrust_state, 0, '是', 1, '否') c_entrust_state,
			 e.n_entrust_remarks,
			 e.d_ntrust_operating_time,
             e.d_ntrust_enddata
	from qs_entrust e, sys_user u, sys_user us
 where us.user_id = e.n_principal
	 and u.user_id = e.n_user_id ");
            if (state == "0" && name != "") //委托人
            {
                sql += " and us.user_name='" + name + "'  ";
            }
            else if (state == "1" && name != "")  //被委托人
            {
                sql += " and u.user_name='" + name + "'  ";
            }

            sql += " ) order by n_entrust_id desc";
            //执行sql并接收返回结果

            DataTable dt = base.Query(sql, "", "", "", "");

            return dt;
        }

        public bool SelectEntTime(string UserId, DateTime time, string entId)
        {
            bool fig = false;
            string sql = string.Format(@"SELECT * FROM THKQS.Qs_Entrust e WHERE e.N_Principal = " + UserId);
            if (!string.IsNullOrEmpty(entId))
            {
                sql += " AND e.n_User_Id =" + entId + " AND e.C_Entrust_state=0";
            }
            sql += string.Format(" AND e.D_Ntrust_start_date <=TO_DATE('{0}', 'yyyy-mm-dd hh24:mi:ss')", time.ToString("yyyy-MM-dd HH:mm:ss"));

            DataTable dt = base.Query(sql, "", "", "", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                fig = true;
            }
            return fig;
        }

        public int GetRecordCount(string UserId, string STATE, string Name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT count(1)
FROM
	THKQS.Qs_Entrust e,
	THKQS.Sys_User u,
	THKQS.Sys_User us
WHERE
    us.USER_ID = e.N_Principal
    AND u.USER_ID = e.N_User_Id ");
            if (STATE == "0")
            {
                strSql.Append(" AND e.N_Principal=" + UserId);
            }
            else
            {
                strSql.Append(" AND e.N_User_Id=" + UserId);
            }

            if (!string.IsNullOrEmpty(Name))
            {
                strSql.Append(" AND (u.USER_NAME like '%" + Name + "%' OR e.C_Entrust_state like  '%" + Name + @"%' OR
			 e.D_Ntrust_start_date like  '%" + Name + "%' OR e.N_Entrust_remarks like  '%" + Name + @"%' OR
			 e.D_ntrust_operating_time like  '%" + Name + @"%')");
            }
            return GetCount(strSql.ToString());
        }

        public DataTable GetListByPage(string UserId, string Name, string STATE, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
    N_Entrust_Id,
    USER_NAME,
    USER_NAMES,
    D_Ntrust_start_date,
    C_Entrust_state,
    N_Entrust_remarks,
    D_ntrust_operating_time
FROM (SELECT
    e.N_Entrust_Id,
    us.USER_NAME,
    u.USER_NAME USER_NAMES,
    e.D_Ntrust_start_date,
    CASE e.C_Entrust_state
    WHEN 0 THEN '是'
    WHEN 1 THEN '否'
    END as C_Entrust_state,
    e.N_Entrust_remarks,
    e.D_ntrust_operating_time
FROM
    THKQS.Qs_Entrust e,
    THKQS.Sys_User u,
    THKQS.Sys_User us
WHERE
    us.USER_ID = e.N_Principal
    AND u.USER_ID = e.N_User_Id ");
            if (STATE == "0")
            {
                strSql.Append(" AND e.N_Principal=" + UserId + " )t WHERE 1=1");
            }
            else
            {
                strSql.Append(" AND e.N_User_Id=" + UserId + " )t WHERE 1=1");
            }

            if (!string.IsNullOrEmpty(Name))
            {
                strSql.Append(" AND (USER_NAMES like '%" + Name + "%' OR C_Entrust_state like  '%" + Name + @"%' OR
			 D_Ntrust_start_date like  '%" + Name + "%' OR N_Entrust_remarks like  '%" + Name + @"%' OR
			 D_ntrust_operating_time like  '%" + Name + @"%')");
            }
            strSql.Append(@" ) TT");
            if (startIndex >= 2)
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1}+1 AND {0}*{1}", startIndex, endIndex);
            }
            else
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1} AND {0}*{1}", startIndex, endIndex);
            }
            return base.Query(strSql.ToString(), "", "", "", "");
        }

        /// <summary>
        ///  查询一条委托记录
        /// </summary>
        /// <param name="model"></param>
        /// <param name="logonName"></param>
        ///<returns></returns>
        public Qs_Entrust SelectModelId(string Id)
        {
            //SQL 语句
            string sql = string.Format(@" select e.n_entrust_id,
			 e.n_principal,
			 e.n_user_id,
			 e.d_ntrust_start_date,
			 e.c_entrust_state,
			 e.n_entrust_remarks,
			 e.d_ntrust_operating_time,
             e.d_ntrust_enddata
	from qs_entrust e
 where e.n_entrust_id=" + Id);

            //DataTable dt = helper.GetDataTable(sql);
            DataTable dt = base.Query(sql, "", "", "", "");
            Qs_Entrust ent = null;

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    ent = new Qs_Entrust();
                    ent.N_Entrust_Id = Convert.ToDecimal(item[0].ToString());
                    ent.N_Principal = Convert.ToDecimal(item[1].ToString());
                    ent.N_User_Id = Convert.ToDecimal(item[2].ToString());

                    ent.D_Ntrust_start_date = Convert.ToDateTime(item[3].ToString());
                    ent.C_Entrust_state = item[4].ToString();

                    ent.N_Entrust_remarks = item[5].ToString();
                    ent.D_ntrust_operating_time = Convert.ToDateTime(item[6].ToString());
                    ent.D_ntrust_enddata = Convert.ToDateTime(item[7].ToString());
                }
            }
            return ent;
        }

        public override bool Update(Qs_Entrust entity)
        {
            var sql = @"UPDATE QS_ENTRUST
SET
	N_PRINCIPAL = {0},
	N_USER_ID = {1},
	D_NTRUST_START_DATE = TO_DATE('{2}','YYYY-MM-DD HH24:MI:SS'),
	C_ENTRUST_STATE = '{3}',
	N_ENTRUST_REMARKS = '{4}',
	D_NTRUST_OPERATING_TIME = TO_DATE('{5}','YYYY-MM-DD HH24:MI:SS'),
	COMPANY_ID = {6},
	D_NTRUST_ENDDATA = TO_DATE('{7}','YYYY-MM-DD HH24:MI:SS')
WHERE
	N_ENTRUST_ID = {8}";
            sql = string.Format(sql
                , entity.N_Principal, entity.N_User_Id, entity.D_Ntrust_start_date.ToString("yyyy-MM-dd HH:mm:ss"), entity.C_Entrust_state, entity.N_Entrust_remarks == null ? "" : entity.N_Entrust_remarks, entity.D_ntrust_operating_time.ToString("yyyy-MM-dd HH:mm:ss"), entity.Company_Id, entity.D_ntrust_enddata.ToString("yyyy-MM-dd HH:mm:ss"), entity.N_Entrust_Id);
            return NoQuery(sql) > 0 ? true : false;
        }

        public override Qs_Entrust Find(object key)
        {
            var entitys = Where("N_ENTRUST_ID = " + key.ToString());
            if (entitys.Count == 0)
            {
                return null;
            }
            return entitys[0];
        }

        public override System.Collections.Generic.List<Qs_Entrust> Where(string whereStr = null, string orderbyStr = null, int? skip = null, int? take = null)
        {
            var sql = "SELECT * FROM QS_ENTRUST";
            if (whereStr != null)
            {
                sql += " WHERE " + whereStr;
            }
            if (orderbyStr != null)
            {
                sql += " ORDER BY " + orderbyStr;
            }

            return ConvertEntity(PageQuery(sql, skip, take));
        }

        public override bool Insert(Model.Qs_Entrust entity)
        {
            entity.N_Entrust_Id = GetSequence("N_Entrust_ID");
            var sql = @"INSERT INTO QS_ENTRUST(
	N_ENTRUST_ID,
	N_PRINCIPAL,
	N_USER_ID,
	D_NTRUST_START_DATE,
	C_ENTRUST_STATE,
	N_ENTRUST_REMARKS,
	D_NTRUST_OPERATING_TIME,
	COMPANY_ID,
	D_NTRUST_ENDDATA
)
VALUES
(
	{8},
	{0},
	{1},
	TO_DATE('{2}','YYYY-MM-DD HH24:MI:SS'),
	'{3}',
	'{4}',
	TO_DATE('{5}','YYYY-MM-DD HH24:MI:SS'),
	{6},
	TO_DATE('{7}','YYYY-MM-DD HH24:MI:SS')
)";
            sql = string.Format(sql
                , entity.N_Principal, entity.N_User_Id, entity.D_Ntrust_start_date.ToString("yyyy-MM-dd HH:mm:ss"), entity.C_Entrust_state, entity.N_Entrust_remarks == null ? "" : entity.N_Entrust_remarks, entity.D_ntrust_operating_time.ToString("yyyy-MM-dd HH:mm:ss"), entity.Company_Id, entity.D_ntrust_enddata.ToString("yyyy-MM-dd HH:mm:ss"), entity.N_Entrust_Id);
            return NoQuery(sql) > 0 ? true : false;
        }
    }
}